---
title: "Wages and Prices"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    css: styles.css
    source: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
```

```{r warning=FALSE, message=FALSE}

library(tidyverse)
library(ggspatial)
library(ggplot2)
library(ggrepel)
library(rnaturalearth)
library(rnaturalearthdata)
library(maptools)
library(geofacet)
library(readr)
library(gganimate)
library(rgdal)
library(ggmap)
library(zoo)
library(DT)

world <- ne_countries(scale = "medium", returnclass = "sf")
RDSfolder = "./data_temp/map.objects/"
waterColor = "lightsteelblue2"

theme_set(theme_bw())

themeParameters <- theme(panel.background = element_rect(fill = waterColor),
          axis.title.y=element_blank(),
          axis.title.x=element_blank(),
          axis.text.x=element_blank(),
          axis.ticks.x=element_blank(),
          axis.text.y=element_blank(),
          axis.ticks.y=element_blank(),
          panel.grid.major = element_line(color = waterColor, linetype = "dotted", size = 0.5))

eu_xlim=c(-12,30)
eu_ylim=c(36,58)

```

```{r warning=FALSE, message=FALSE}

wide_nominal_wages <- read_delim("./files/nominal_wages.csv", delim=";")

long_nominal_wages <- wide_nominal_wages %>%
  gather(city, wages, Antwerp:Hamburg)

nominal_wages_ma <- long_nominal_wages  %>%
  group_by(city) %>%
  mutate(wages_ma = rollmean(wages, k = 15, fill = NA))

nominal_wages <- nominal_wages_ma %>%
  filter(year >= 1400 )

##########

wide_cp_index <- read_delim("./files/consumerprice_index.csv", delim=";")

long_cp_index <- wide_cp_index %>%
  gather(city, index, Antwerp:Hamburg)

cp_index_ma <- long_cp_index  %>%
  group_by(city) %>%
  mutate(index_ma = rollmean(index, k = 15, fill = NA))

cp_index <- cp_index_ma %>%
  filter(year >= 1400 )

##########

wide_real_wages <- read_delim("./files/real_wages.csv", delim=";")

long_real_wages <- wide_real_wages %>%
  gather(city, wages, Antwerp:Hamburg)

real_wages_ma <- long_real_wages  %>%
  group_by(city) %>%
  mutate(wages_ma = rollmean(wages, k = 15, fill = NA))

real_wages <- real_wages_ma %>%
  filter(year >= 1400) %>%
  filter (city != "Milan") %>% #wrong data
  filter (city != "Oxford") #wrong data

##########

wide_welfare_ratio <- read_delim("./files/welfare_ratio.csv", delim=";")

long_welfare_ratio <- wide_welfare_ratio %>%
  gather(city, ratio, Antwerp:Hamburg)
  
welfare_ratio_ma <- long_welfare_ratio  %>%
  group_by(city) %>%
  mutate(ratio_ma = rollmean(ratio, k = 15, fill = NA))

welfare_ratio <- welfare_ratio_ma %>%
  filter(year >= 1400)

##########

starting_grid <- nominal_wages %>%
  select(city) %>% unique 

#grid_design(data = starting_grid)

mygrid <- data.frame(
  name = c("Antwerp", "Gdansk", "Amsterdam", "London", "Warsaw", "Leipzig", "Paris", "Strasbourg", "Augsburg", "Munich", "Vienna", "Krakow", "Lwow", "Florence", "Naples", "Valencia", "Madrid", "Hamburg", "Milan", "Oxford"),
  row = c(2, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 4, 5, 5, 5, 1, 4, 2),
  col = c(3, 7, 4, 2, 7, 5, 2, 3, 4, 5, 6, 7, 8, 5, 5, 2, 1, 4, 4, 1),
  code = c("Antwerp", "Gdansk", "Amsterdam", "London", "Warsaw", "Leipzig", "Paris", "Strasbourg", "Augsburg", "Munich", "Vienna", "Krakow", "Lwow", "Florence", "Naples", "Valencia", "Madrid", "Hamburg", "Milan", "Oxford"),
  stringsAsFactors = FALSE
)

```

Intro
=====================================



**Provenance of data**

The data I use here come from a study by the economic historian Robert C. Allen called "The Great Divergence in European Wages and Prices from the Middle Ages to the First World War." They include nominal wages, consumer price indices, real wages and welfare ratios of 20 European cities from the late 13th century to the early 20th century.

I downloaded the data as an Excel spreadsheet from here: https://dataverse.harvard.edu/file.xhtml?persistentId=doi:10.7910/DVN/TMZGSF/GAFFOK&version=1.0

* The nominal wages in the data are from building craftsmen, which are the most recorded in price histories. These are daily wages, which have been converted to grams of silver by the author for comparison.

* In order to compare the purchasing power of nominal wages, a consumer price index is needed. For this purpose, the author created a basket of goods with 12 different items - e.g. for example bread (30% of spending), beer/wine (20%), meat (15%) etc. The prices for the consumer price index are averages for Strasbourg in 1745–1754.

* The real wage equals the nominal wage divided by the consumer price index. The real wage shows proportional changes and relative levels. It has no absolute interpretation.

* To make the results more meaningful, the author calculates welfare ratios in addition to the real wages. The welfare ratio is average annual earnings divided by the cost of a poverty line consumption bundle for a family. 

**Data quality**

* Nominal wages: The problem here is that often only part of the wages were paid in money and the rest in food or even housing. This was managed very differently, and not always noted in the account books. Therefore, a comparison is not always given.

* Consumer price index: The selection and weighting of the different items of the basket can only be based on very rough simplifications and assumptions. In addition, different cuisines and climates must be taken into account for a European comparison.

* Real wages: If the data for nominal wages and the consumer price index are not correct, real wages are not correct either.

* Welfare ratios: To calculate the welfare ratios, the author makes a series of assumptions: Number of working days per year (250), the size of a family (a man, a woman and two children), the spending on rent (5%) etc.

**Preparation of data**

* Splitting the Excel spreadsheet into 4 different CSV files
* Reorganization according to the principles of tidy data
* Deleting of obviously incorrect data
* Adding of a 15-year moving average to smooth out short-term fluctuations
* Filtering out all records before 1400 (because there are so few)
* And finally, the creation of a geo grid for the visualizations




Visualizations {.storyboard}
=====================================

### Nominal wages {data-commentary-width=400}

```{r  warning=FALSE, message=FALSE}

ggplot(nominal_wages, aes(x = year, y = wages_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Nominal wages of building craftsmen, 1400-1913",
    x = "Year",
    y = "Grams of silver per day") +
    theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
By 1600, nominal wages in southern European cities, Amsterdam and Antwerp, as well as London and Paris, were already two times higher than in the rest of Europe (about 10 versus 5 grams of silver per day).

In the 19th century, a strong increase in nominal wages can be observed in almost all cities. But it was particularly strong in London: Here, nominal wages were 4 times higher around 1900 than at the beginning of the century, at about 100 grams of silver per day.

### Consumer price index {data-commentary-width=400}

```{r  warning=FALSE, message=FALSE}

ggplot(cp_index, aes(x = year, y = index_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Consumer Price Index (Strasbourg, 1745-1754 = 100)",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
Around 1600, we can see particularly strongly in Western Europe and even more so in Spain, the effects of the price revolution due to the influx of gold and silver from America.

It is especially noticeable here that prices in northwestern Europe, unlike the rest of Europe, did not decline afterwards.

### Real wages {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

ggplot(real_wages, aes(x = year, y = wages_ma)) +
  geom_line(color = "tomato2") +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Real wages of building craftsmen, 1400-1913",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***

Here we can see that in almost all cities the real wages collapsed between 1400 and 1800. The only exceptions are London, Amsterdam, Antwerp and Warsaw. But only London was able at the end of the 18th century to regain its late medieval high level.

In the 19th century, on the other hand, the real wages rose strongly but from very different levels.

### Welfare ratios {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

ggplot(welfare_ratio, aes(x = year, y = ratio_ma)) +
  facet_geo(~ city, grid = mygrid, label = "name") +
  geom_rect(aes(xmin = 1500, xmax = 1550, ymin = 0, ymax = 3), fill = "khaki1", alpha = 0.01) +
  geom_line(color = "tomato2") +
  labs(title = "Welfare ratios of building craftsmen, 1400-1913",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
As the welfare ratio is only a specially scaled real wage index, it also has the same shape, but of course with different values.

We see here, above all, that the differences were smallest at the beginning of the 16th century (in yellow).

Changes {.storyboard}
=====================================

### Standard deviation of the welfare ratios {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

welfare_ratio_stat <- welfare_ratio %>%
  ungroup() %>%
  select(year,ratio) %>%
  filter(year >= 1500)

deviation <- aggregate(. ~ year, welfare_ratio_stat, function(x) c(sd = sd(x)))

deviation <- deviation %>%
  rename(sd = ratio) %>%
  mutate(sd_ma = rollmean(sd, k = 15, fill = NA))

ggplot(deviation, aes(x = year, y = sd_ma)) +
  geom_line(color = "tomato2") +
  labs(title = "Standard deviation of the welfare ratios, 1500-1913",
    x = "Year",
    y = "")

```

***
To accurately test the assumption that differences were smallest in the early 16th century, I calculate the standard deviation of the welfare ratios.

Here we can see that the standard deviation is lowest in 1525 at 0.3 and highest in 1900 at about 0.85. With this knowledge, I now calculate the mean of the welfare ratios in 1525.

### Changes of the welfare ratios in percent {data-commentary-width=400}

```{r warning=FALSE, message=FALSE}

welfare_ratio_1525 <- welfare_ratio %>%
  filter(year == "1525") %>%
  filter(! is.na(ratio))

ratio_mean_1525 <- mean(welfare_ratio_1525$ratio)

welfare_ratio_perc <- welfare_ratio %>%
  mutate(percentage = (ratio / ratio_mean_1525 -1) * 100) %>%
  mutate(percentage_ma = rollmean(percentage, k = 15, fill = NA)) %>%
  filter(year >= 1500)

ggplot(welfare_ratio_perc, aes(x = year, y = percentage_ma)) +
  geom_ribbon(aes(ymin = pmin(percentage_ma, 0), ymax = 0), fill = "red3", col = "red3", alpha = 0.5, size = 0.3) +
  geom_ribbon(aes(ymin = 0, ymax = pmax(percentage_ma, 0)), fill = "green4", col = "green4", alpha = 0.5, size = 0.3) +
  geom_line(aes(y = 0), color = "gray20", size = 0.3) +
  facet_geo(~ city, grid = mygrid, label = "name") +
  labs(title = "Changes of the welfare ratios in percent, 1500-1913",
    subtitle = "Base: 1.83 at 1525",
    x = "Year",
    y = "") +
  theme(axis.text.x = element_text(angle = 90, size = 7), axis.text.y = element_text(size = 7), strip.text.x = element_text(size = 7))

```

***
And based on this mean (1.83), the annual percentage changes in the welfare ratios can now be calculated.

The declines until 1800 are quite dramatic in many cities. Only for London and Antwerp are relevant green areas visible in this period.

Maps {.storyboard}
=====================================

### Welfare ratios at 1525 {data-commentary-width=400}

```{r  warning=FALSE, message=FALSE}

#rivers <- readOGR("./data_temp/layer.riverData", "ne_50m_rivers_lake_centerlines")
#rivers_df <- fortify(rivers)

#saveRDS(rivers_df, paste0(RDSfolder,"rivers_df.rds"))

rivers_df <- readRDS(paste0(RDSfolder,"rivers_df.rds"))

riversData <- rivers_df

cities <- read_csv("./files/cities_coords.csv")

map <- ggplot(data = world) +
  geom_sf(fill="white", color="white") +
  coord_sf(xlim = eu_xlim, ylim = eu_ylim, expand = TRUE) +
  geom_path(data = riversData, aes(x = long, y = lat, group = group), color = waterColor, size = .1) +
  scale_radius(range = c(1, 10), limits = c(0.1, 5)) +
  labs(size = "Welfare ratios") +
  themeParameters

welfare_ratio_coords <- welfare_ratio %>%
  left_join(cities, by="city")

welfare_ratio_coords_1525 <- welfare_ratio_coords %>%
  filter(year == "1525") %>%
  filter(! is.na(ratio_ma))

map1525 <- map +
  geom_point(data = welfare_ratio_coords_1525, aes(x = lng, y = lat, size = ratio_ma), color = "tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1525, aes(x=lng, y=lat, label = city), color = "black", size = 2.5, segment.size = 0.25)

map1525

```

***
In 1525, where the standard deviation is the lowest, we can see that the welfare ratio in almost all cities is close to 2.

### Welfare ratios at 1750 {data-commentary-width=400}

```{r  warning=FALSE}

welfare_ratio_coords_1750 <- welfare_ratio_coords  %>%
  filter(year=="1750") %>%
  filter(! is.na(ratio_ma))

map1750 <- map +
  geom_point(data = welfare_ratio_coords_1750, aes(x=lng, y=lat, color=ratio, size=ratio_ma), color="tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1750, aes(x=lng, y=lat, label = city), color="black", size = 2.5, segment.size=0.25)

map1750

```

***
225 years later, the situation is quite different. The welfare ratio has fallen to about 1 in many cities, but in a few, like London, Antwerp or Amsterdam, it has increased very slightly.
London has the highest welfare ratio at 2.44, but in some cities, like Strasbourg, Munich or Lwow, the welfare ratio has even fallen below 1.

### Welfare ratios at 1850 {data-commentary-width=400}

```{r warning=FALSE}

welfare_ratio_coords_1850 <- welfare_ratio_coords  %>%
  filter(year=="1850") %>%
  filter(! is.na(ratio_ma))

map1850 <- map +
  geom_point(data = welfare_ratio_coords_1850, aes(x=lng, y=lat, color=ratio, size=ratio_ma), color="tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1850, aes(x=lng, y=lat, label = city), color="black", size = 2.5, segment.size=0.25)

map1850

```

***
At 1850, unfortunately, there is no more data from many cities.
The welfare ratio is now highest in Warsaw at 3.26 and lowest in Florence at 0.71, and the differences have continued to grow.

### Welfare ratios at 1900 {data-commentary-width=400}

```{r warning=FALSE}

welfare_ratio_coords_1900 <- welfare_ratio_coords  %>%
  filter(year=="1900") %>%
  filter(! is.na(ratio_ma))

map1900 <- map +
  geom_point(data = welfare_ratio_coords_1900, aes(x=lng, y=lat, size=ratio_ma), color="tomato2") +
  geom_label_repel(data = welfare_ratio_coords_1900, aes(x=lng, y=lat, label = city), color="black", size = 2.5, segment.size = 0.25) 

map1900

```

***
At 1900, the differences are similar to those in 1850, London has again the highest welfare ratio at 4.10 and Florence still the lowest at 1.43.


Data {.storyboard}
=====================================

### Nominal wages: `nominal_wages` {data-commentary-width=400}

```{r warning=FALSE}

datatable(nominal_wages, options = list(pageLength = 50))

```

### Consumer price index: `cp_index` {data-commentary-width=400}

```{r warning=FALSE}

datatable(cp_index, options = list(pageLength = 50))

```

### Real wages: `real_wages` {data-commentary-width=400}

```{r warning=FALSE}

datatable(real_wages, options = list(pageLength = 50))

```

### Welfare ratios: `welfare_ratio` {data-commentary-width=400}

```{r warning=FALSE}

datatable(welfare_ratio, options = list(pageLength = 50))

```